package com.topisv.tms.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.topisv.tms.costants.Constants;
import com.topisv.tms.entity.dto.ExcelDto;
import com.topisv.tms.utils.ColorUtil;
import com.topisv.tms.utils.InitProperties;

/**
 * Class: exportUtil.java Description: 导出工具类
 * 
 * @author chenhong
 */
public class ExcelUtils {
    private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    
    public final static String TEMPPATH = InitProperties.get("excel.download.path");
    
    public static void writeExcel(String[] title, String filepath, String sheetname, List<Object[]> list) {
        writeExcel(title, filepath, null, sheetname, list);
    }
    
    public static void writeExcel(String[] title, String filepath, String title_page, String sheetname,
        List<Object[]> list)
    {
        OutputStream os = null;
        try {
            os = new FileOutputStream(TEMPPATH + filepath);
            writeExcel(title, title_page, sheetname, list, os);
        } catch (Exception e) {
            logger.error("writeExcel case some error.", e);
        } finally {
            if (null != os) {
                try {
                    os.close();
                    os = null;
                } catch (IOException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                }
            }
        }
    }
    
    /**
     * 不包含标题的sheet
     * 
     * @param title
     * @param sheetname
     * @param list
     * @param os
     */
    public static void writeExcel(String[] title, String sheetname, List<Object[]> list, OutputStream os) {
        writeExcel(title, null, sheetname, list, os);
    }
    
    public static void writeExcel(String[] title, String title_page, String sheetname, List<Object[]> list,
        OutputStream os)
    {
        WritableWorkbook wwb = null;
        
        try {
            wwb = Workbook.createWorkbook(os);
            
            // 添加工作表，并设置第一个Sheet的名字
            WritableSheet sheet = wwb.createSheet(sheetname, 0);
            
            // 页标题行
            WritableFont font_pageTitle = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
                13, // 字号
                WritableFont.NO_BOLD, // 粗体
                false, // 斜体
                UnderlineStyle.NO_UNDERLINE, // 下划线
                Colour.BLACK, // 字体颜色
                ScriptStyle.NORMAL_SCRIPT);
            int startRow = 0;
            if (!StringUtils.isBlank(title_page)) {
                WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
                fromat_pageTitle.setAlignment(Alignment.CENTRE); // 设置文字水平居中
                fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                
                sheet.addCell(new Label(0, 0, title_page, fromat_pageTitle)); // 合并单元格
                sheet.mergeCells(0, 0, title.length - 1, 0); // 合并单元格
                startRow++;
            }
            sheet.setRowView(0, 800); // 设置行的高度
            
            // 栏目标题行
            WritableFont font_columnTitle = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
                WritableFont.DEFAULT_POINT_SIZE, // 字号
                WritableFont.BOLD, // 粗体
                false, // 斜体
                UnderlineStyle.NO_UNDERLINE, // 下划线
                Colour.BLACK, // 字体颜色
                ScriptStyle.NORMAL_SCRIPT);
            
            WritableCellFormat fromat_columnTitle = new WritableCellFormat(font_columnTitle);
            fromat_columnTitle.setAlignment(Alignment.CENTRE); // 设置文字水平居中
            fromat_columnTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
            fromat_columnTitle.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
            fromat_columnTitle.setBackground(ColorUtil.getNearestColour("#66CCFF")); // 设置单元格背景颜色
            
            for (int i = 0; i < title.length; i++) {
                Label label = new Label(i, startRow, title[i], fromat_columnTitle);
                sheet.addCell(label);
                
                sheet.setColumnView(i, 15); // 设置列的宽度
                sheet.setRowView(1, 400); // 设置行的高度
            }
            // 表格数据行
            
            // 单元格格式1——正常字体
            WritableCellFormat format_cell_1 = new WritableCellFormat();
            format_cell_1.setAlignment(Alignment.CENTRE); // 设置文字水平居中
            format_cell_1.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
            format_cell_1.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
            
            // 单元格格式2 ——红色背景
            WritableCellFormat format_cell_2 = new WritableCellFormat();
            format_cell_2.setAlignment(Alignment.CENTRE); // 设置文字水平居中
            format_cell_2.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
            format_cell_2.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
            format_cell_2.setBackground(Colour.RED);
            
            if (list != null && list.size() > 0) {
                
                for (int i = 0; i < list.size(); i++) {
                    
                    Object[] obj = (Object[])list.get(i);
                    for (int j = 0; j < obj.length - 1; j++) {
                        String value = (obj[j] == null ? "" : obj[j].toString());
                        Label label = null;
                        if (obj[obj.length - 1] != null) {
                            label = new Label(j, i + startRow + 1, value, format_cell_2);
                        } else {
                            label = new Label(j, i + startRow + 1, value, format_cell_1);
                        }
                        sheet.addCell(label);
                    }
                    sheet.setRowView(i + 2, 400); // 设置行的高度
                    
                }
                
            }
            
            wwb.write(); // 写入数据
        } catch (Exception e) {
            logger.error("ExcelUtils writeExcel case some error.", e);
        } finally {
            if (null != wwb) {
                try {
                    wwb.close();
                    wwb = null;
                } catch (WriteException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                } catch (IOException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                }
            }
        }
    }
    
    public static void writeExcel(String fileName, String filePath, List<ExcelDto> excelDtoList) {
        // 创建Excel工作薄
        WritableWorkbook wwb = null;
        // 新建的xls文件输出路径
        OutputStream os = null;
        try {
            os = new FileOutputStream(TEMPPATH + filePath);
            wwb = Workbook.createWorkbook(os);
            
            for (int k = 0; k < excelDtoList.size(); k++) {
                ExcelDto excelDto = excelDtoList.get(k);
                List<Object[]> list = excelDto.getList();
                String title = excelDto.getTitle();
                String title2 = excelDto.getTitle2();
                String[] columnTitle = excelDto.getColumnTitle();
                String sheetName = excelDto.getSheetName();
                int[] columnWidth = excelDto.getColumnWidth();
                int rowHeight = excelDto.getRowHeight();
                
                // 添加工作表，并设置第一个Sheet的名字
                WritableSheet sheet = wwb.createSheet(sheetName, k);
                
                // 大标题行
                WritableFont font_title1 = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
                    13, // 字号
                    WritableFont.NO_BOLD, // 粗体
                    false, // 斜体
                    UnderlineStyle.NO_UNDERLINE, // 下划线
                    Colour.BLACK, // 字体颜色
                    ScriptStyle.NORMAL_SCRIPT);
                
                WritableCellFormat fromat_title1 = new WritableCellFormat(font_title1);
                fromat_title1.setAlignment(Alignment.CENTRE); // 设置文字水平居中
                fromat_title1.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                
                sheet.addCell(new Label(0, 0, title, fromat_title1)); // 合并单元格
                sheet.mergeCells(0, 0, columnTitle.length - 1, 0); // 合并单元格
                sheet.setRowView(0, 500); // 设置行的高度
                
                // 小标题行
                WritableFont font_title2 = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
                    13, // 字号
                    WritableFont.NO_BOLD, // 粗体
                    false, // 斜体
                    UnderlineStyle.NO_UNDERLINE, // 下划线
                    Colour.BLACK, // 字体颜色
                    ScriptStyle.NORMAL_SCRIPT);
                
                WritableCellFormat fromat_title2 = new WritableCellFormat(font_title2);
                fromat_title2.setAlignment(Alignment.RIGHT); // 设置文字水平居中
                fromat_title2.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                
                sheet.addCell(new Label(0, 1, title2, fromat_title2)); // 合并单元格
                sheet.mergeCells(0, 1, columnTitle.length - 1, 1); // 合并单元格
                sheet.setRowView(0, 500); // 设置行的高度
                
                // 栏目标题行
                WritableFont font_columnTitle = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
                    WritableFont.DEFAULT_POINT_SIZE, // 字号
                    WritableFont.BOLD, // 粗体
                    false, // 斜体
                    UnderlineStyle.NO_UNDERLINE, // 下划线
                    Colour.BLACK, // 字体颜色
                    ScriptStyle.NORMAL_SCRIPT);
                
                WritableCellFormat fromat_columnTitle = new WritableCellFormat(font_columnTitle);
                fromat_columnTitle.setAlignment(Alignment.CENTRE); // 设置文字水平居中
                fromat_columnTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                fromat_columnTitle.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
                fromat_columnTitle.setBackground(ColorUtil.getNearestColour("#66CCFF")); // 设置单元格背景颜色
                
                for (int i = 0; i < columnTitle.length; i++) {
                    Label label = new Label(i, 2, columnTitle[i], fromat_columnTitle);
                    sheet.addCell(label);
                    sheet.setColumnView(i, columnWidth[i]); // 设置列的宽度
                    sheet.setRowView(1, rowHeight); // 设置行的高度
                }
                
                // 单元格格式1-正常格式
                WritableCellFormat format_cell_1 = new WritableCellFormat();
                format_cell_1.setAlignment(Alignment.CENTRE); // 设置文字水平居中
                format_cell_1.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                format_cell_1.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
                
                // 单元格格式2-红色背景
                WritableCellFormat format_cell_2 = new WritableCellFormat();
                format_cell_2.setAlignment(Alignment.CENTRE); // 设置文字水平居中
                format_cell_2.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
                format_cell_2.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
                format_cell_2.setBackground(Colour.ROSE);
                
                if (list != null && list.size() > 0) {
                    for (int i = 0; i < list.size(); i++) {
                        Object[] obj = (Object[])list.get(i);
                        
                        for (int j = 0; j < obj.length - 1; j++) {
                            String value = (obj[j] == null ? "" : obj[j].toString());
                            
                            Label label = null;
                            if (obj[obj.length - 1] != null) {
                                label = new Label(j, i + 3, value, format_cell_2);
                            } else {
                                label = new Label(j, i + 3, value, format_cell_1);
                            }
                            
                            sheet.addCell(label);
                        }
                        sheet.setRowView(i + 2, 400); // 设置行的高度
                        
                    }
                }
            }
            
            wwb.write(); // 写入数据
            
        } catch (Exception e) {
            logger.error("ExcelUtils writeExcel case some error.", e);
        } finally {
            if (null != wwb) {
                try {
                    wwb.close();
                    wwb = null;
                } catch (WriteException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                } catch (IOException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                }
            }
            if (null != os) {
                try {
                    os.close();
                    os = null;
                } catch (IOException e) {
                    logger.error("ExcelUtils writeExcel exception. ", e);
                }
            }
        }
    }
    
    public static void writeOutZip(OutputStream out, File file)
        throws Exception
    {
        ZipOutputStream zipOut = new ZipOutputStream(out);
        zipOut.putNextEntry(new ZipEntry(file.getName()));
        InputStream in = new FileInputStream(file);
        IOUtils.copy(in, zipOut);
        IOUtils.closeQuietly(in);
        IOUtils.closeQuietly(zipOut);
    }
    
    /**
     * 将数据写入新建的xls中，可适用于一个xls有多个导出多模板<br/>
     * 如果数据超过5W，会进行分sheet输出。
     * 
     * @param fileFullName 写入文件的全路径名称
     * @param fileFullName
     * @param tablePojo
     * @return [参数说明]
     * 
     * @return WritableWorkbook [返回类型说明]
     * @exception throws [违例类型] [违例说明]
     * @see [类、类#方法、类#成员]
     * @author zhangpeijun
     */
    public static void createAndWrite2Excel(String fileFullName, List<TablePojo> tablePojo) {
        WritableWorkbook wwb = null;
        if (StringUtils.isNotEmpty(fileFullName) && null != tablePojo && tablePojo.size() > 0) {
            try {
                File file = new File(fileFullName);
                wwb = Workbook.createWorkbook(file);
                String sheetName = null;
                List<String[]> bodys = null;
                List<String[]> heads = null;
                WritableSheet ws = null;
                List<String[]> rows = null;
                int sheetIdx = 0;
                // 分sheet 模板进行循环
                for (TablePojo table : tablePojo) {
                    if (null == table) {
                        continue;
                    }
                    sheetName = StringUtils.isNotEmpty(table.getName()) ? table.getName()
                        : Constants.SHEET_DEFAULT_NAME;
                    bodys = table.getBodys();
                    heads = table.getHeads();
                    // 判断数据量是否超过单个sheet的最大值Constants.SHEET_MAX_ROW
                    int size = null == bodys ? 0 : bodys.size();
                    int headsSize = null == heads ? 0 : heads.size();
                    int sheetNum = (0 == size % Constants.SHEET_MAX_ROW) ? size / Constants.SHEET_MAX_ROW : size
                        / Constants.SHEET_MAX_ROW + 1;
                    if (size > 0) {
                        // 每个模板数据，单个sheet 只装载 Constants.SHEET_MAX_ROW 数据量
                        for (int i = 0; i < sheetNum; i++) {
                            // 初始化先加入表头。默认初始的大小
                            rows = new ArrayList<String[]>(size > Constants.SHEET_MAX_ROW ? Constants.SHEET_MAX_ROW
                                + headsSize : size + headsSize);
                            if (null != heads) {
                                for (String[] head : heads) {
                                    rows.add(head);
                                }
                            }
                            // 遍历数据数据集。
                            for (int j = i * Constants.SHEET_MAX_ROW; j < (i + 1) * Constants.SHEET_MAX_ROW; j++) {
                                if (j == size) {
                                    break;
                                }
                                rows.add(bodys.get(j));
                            }
                            ws = wwb.createSheet(i == 0 ? sheetName : sheetName + i, sheetIdx);
                            write2Sheet(ws, rows);
                            // 最后一个sheet，不需要自增
                            if (i < sheetNum - 1) {
                                sheetIdx++;
                            }
                        }
                    } else {
                        // 数据为空，仅仅写入表头即可
                        rows = new ArrayList<String[]>();
                        if (null != heads) {
                            for (String[] head : heads) {
                                rows.add(head);
                            }
                        }
                        ws = wwb.createSheet(sheetName, sheetIdx);
                        write2Sheet(ws, rows);
                    }
                    // 多模板进行sheet+1
                    sheetIdx++;
                }
                // 写入文件
                wwb.write();
            } catch (IOException e) {
                logger.error("Create and write to excel exception.", e);
            } finally {
                if (null != wwb) {
                    try {
                        wwb.close();
                        wwb = null;
                    } catch (WriteException e) {
                        logger.error("Create and write to excel exception.", e);
                    } catch (IOException e) {
                        logger.error("Create and write to excel exception.", e);
                    }
                }
            }
        } else {
            logger.info("Write to excel param is null.");
        }
    }
    
    /**
     * 写入数据到sheet
     * 
     * @param sheet
     * @param datas [参数说明]
     * 
     * @return void [返回类型说明]
     * @exception throws [违例类型] [违例说明]
     * @see [类、类#方法、类#成员]
     */
    private static void write2Sheet(WritableSheet sheet, List<String[]> datas) {
        int size = datas.size();
        for (int i = 0; i < size; i++) {
            write2Row(sheet, i, datas.get(i));
        }
    }
    
    /**
     * 写入行列数据
     * 
     * @param sheet
     * @param row
     * @param datas [参数说明]
     * 
     * @return void [返回类型说明]
     * @exception throws [违例类型] [违例说明]
     * @see [类、类#方法、类#成员]
     */
    private static void write2Row(WritableSheet sheet, int row, String[] datas) {
        if (null != sheet && null != datas && datas.length > 0) {
            try {
                int cSize = datas.length;
                String data = null;
                for (int j = 0; j < cSize; j++) {
                    data = datas[j];
                    sheet.addCell(new Label(j, row, null != data ? data : ""));
                }
            } catch (WriteException e) {
                logger.error("Write row to sheet WriteException ", e);
            }
        }
    }
    
}
